package org.meichao.utils;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtils {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * excel转List
     * @param inputStream excel输入流
     * @param sheetName sheet名字
     * @param titleNum 表头的行数 从0开始
     * @param ignoreRow 忽略几行 从0开始
     * @return
     */
    public static List<JSONObject> excelToList(InputStream inputStream, String sheetName, int titleNum, int ignoreRow) {
        List<JSONObject> resultList = new ArrayList<>();
        try {
            //根据Excel数据源创建WorkBook
            POIFSFileSystem fs = new POIFSFileSystem(inputStream);
            //文档对象
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            //哪个sheet
            HSSFSheet sheet = wb.getSheet(sheetName);
            //总行数
            int rows = sheet.getLastRowNum();
            //表头
            Row title = sheet.getRow(titleNum);
            //列数
            int columns = title.getPhysicalNumberOfCells();
            LOGGER.info("------excel转list开始，总行数：{}，表头行数：{}，列数：{}", rows, title, columns);
            //sheet转list
            for (int i = ignoreRow; i < rows; i++) {
                JSONObject temp = new JSONObject();
                //获取行
                HSSFRow row = sheet.getRow(i);
                //循环每一列
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    String content = "";
                    if (cell != null) {
                        //设置单元格属性
                        cell.setCellType(CellType.STRING);
                        content = cell.getStringCellValue().trim();
                    }
                    //格式：列表头：单元格数据
                    temp.put(title.getCell(j).getStringCellValue(), content);
                }
                resultList.add(temp);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        LOGGER.info("------excel转list结束,转换行数：{}", resultList.size());
        return resultList;
    }
}
